package com.sxtbbs.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sxtbbs.bean.BBSBlog;
import com.sxtbbs.bean.BBSBoard;
import com.sxtbbs.bean.BBSChildboard;
import com.sxtbbs.bean.BBSReplyBlog;
import com.sxtbbs.bean.BBSUser;
import com.sxtbbs.bean.ProvCity;
import com.sxtbbs.dao.DBDao;
import com.sxtbbs.dao.UserDao;
import com.sxtbbs.util.DBDaoFactory;
import com.sxtbbs.util.MD5Encryption;

public class UserDaoImpl implements UserDao {

	private DBDao dbDao = DBDaoFactory.getDBDao();
	
	/**
	 * 获得板块信息
	 */
	@Override
	public List<BBSBoard> getBoard() {
		List<BBSBoard> boardList = new ArrayList<BBSBoard>();
		String sql = "select * from bbsboard where boaflag = 1";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				BBSBoard bbsBoard = new BBSBoard();
				bbsBoard.setBoaid(rs.getString("boaid"));
				bbsBoard.setBoaname(rs.getString("boaname"));
				bbsBoard.setBoauname(rs.getString("boauname"));
				boardList.add(bbsBoard);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return boardList;
	}

	/**
	 * 获得子版块信息
	 */
	@Override
	public List<BBSChildboard> getChildBoard() {
		List<BBSChildboard> bbsChildboardList = new ArrayList<BBSChildboard>();
		String sql = "SELECT cb.cboid,cboname,cnt,boaid,bloid,blotitle,bloinputdate,bloinputtime,uname ";
		sql += "FROM bbschildboard cb LEFT JOIN (SELECT *,COUNT(bloid) cnt FROM ( ";
		sql += "SELECT bloid,blotitle,bloinputdate,bloinputtime,uname,cboid FROM bbsblog ";
		sql += "WHERE bloisdelete='0' ORDER BY bloinputdate DESC,bloinputtime DESC) t GROUP BY cboid) t2 ON cb.cboid = t2.cboid ";
		sql += "WHERE cboflag = '1' ";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				BBSChildboard bbsChildboard = new BBSChildboard();
				bbsChildboard.setCboid(rs.getString("cboid"));
				bbsChildboard.setCboname(rs.getString("cboname"));
				bbsChildboard.setCnt(rs.getInt("cnt"));
				bbsChildboard.setBoaid(rs.getString("boaid"));
				BBSBlog b = new BBSBlog();
				b.setBloid(rs.getInt("bloid"));
				//bbsChildboard.getBbsBlog().setBloid(rs.getInt("bloid"));
				b.setBlotitle(rs.getString("blotitle"));
				b.setBloinputdate(rs.getString("bloinputdate"));
				b.setBloinputtime(rs.getString("bloinputtime"));
				b.setUname(rs.getString("uname"));
				bbsChildboard.setBbsBlog(b);
				bbsChildboardList.add(bbsChildboard);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		
		return bbsChildboardList;
	}

	/**
	 * 获得用户信息
	 */
	@Override
	public BBSUser getUser(String uname) {
		String sql = "select uname, upass,uregdate,uphoto from bbsuser where uname = " + "'" + uname + "'";
		ResultSet rs = dbDao.excuteSelect(sql);
		BBSUser bbsUser = null;
		try {
			if(rs.next()) {
				bbsUser = new BBSUser();
				bbsUser.setUuname(rs.getString("uname"));
				bbsUser.setUpass(rs.getString("upass"));
				bbsUser.setUregdate(rs.getString("uregdate"));
				bbsUser.setUphoto(rs.getString("uphoto"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return bbsUser;
	}

	/**
	 * 用户注册
	 */
	@Override
	public void addUser(BBSUser bbsUser) {
		String sql = "insert into bbsuser value (?,?,?,?,?,?,CURRENT_DATE(),CURRENT_TIME(),?,?)";
		dbDao.excuteDML(sql, bbsUser.getUuname(), MD5Encryption.MD5Encode(bbsUser.getUpass()), 
				bbsUser.getUprov(), bbsUser.getUcity(), bbsUser.getUgender(),
				bbsUser.getUphoto(),"0","0");
	}

	/**
	 * 取出所有省份
	 */
	@Override
	public List<ProvCity> getProv() {
		List<ProvCity> listProv = new ArrayList<ProvCity>();
		String sql = "select * from bbsprovince";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				ProvCity prov = new ProvCity();
				prov.setId(rs.getString("proid"));
				prov.setName(rs.getString("proname"));
				listProv.add(prov);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return listProv;
	}

	
	/**
	 * 取出所有城市
	 */
	@Override
	public List<ProvCity> getCity(String proid) {
		List<ProvCity> listCity = new ArrayList<ProvCity>();
		String sql = "select * from bbscity where father = " + "'" + proid + "'";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				ProvCity city = new ProvCity();
				city.setId(rs.getString("citid"));
				city.setName(rs.getString("citname"));
				listCity.add(city);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return listCity;
	}

	/**
	 * 取出所有的帖子
	 */
	@Override
	public List<BBSBlog> getBlog(String cboid, int startPos, int pageSize) {
		List<BBSBlog> bbsBlogList = new ArrayList<BBSBlog>();
		String sql = "SELECT b.bloid, blotitle, uname, cnt FROM bbsblog b ";
		sql+= "LEFT JOIN (";
		sql += "SELECT bloid, COUNT(rblid) cnt FROM bbsreplyblog ";
		sql += " WHERE rblisdelete = '0'";
		sql += " GROUP BY bloid ";
		sql += ") rb ON b.bloid = rb.bloid ";
		sql += " WHERE cboid = '"+ cboid +"' AND bloisdelete = '0'";
		sql += " limit " + startPos + ", " + pageSize;
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()){
				BBSBlog bbsBlog = new BBSBlog();
				bbsBlog.setBloid(rs.getInt("bloid"));
				bbsBlog.setBlotitle(rs.getString("blotitle"));
				bbsBlog.setUname(rs.getString("uname"));
				bbsBlog.setCnt(rs.getInt("cnt"));
				bbsBlogList.add(bbsBlog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		
		return bbsBlogList;
		
	}

	/**
	 * 取出所有帖子详情
	 */
	@Override
	public BBSBlog getBlogDetail(String bloid) {
		BBSBlog bbsBlogDatail = new BBSBlog();
		String sql = "select uname,blotitle, blocontent, "
				+ "bloinputdate, bloinputtime, blolastmodifydate,"
				+ "blolastmodifytime from bbsblog where bloid = " +  "'" + bloid + "'";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				bbsBlogDatail = new BBSBlog();
				bbsBlogDatail.setBlocontent(rs.getString("blocontent"));
				bbsBlogDatail.setBlotitle(rs.getString("blotitle"));
				bbsBlogDatail.setBloinputdate(rs.getString("bloinputdate"));
				bbsBlogDatail.setBloinputtime(rs.getString("bloinputtime"));
				bbsBlogDatail.setBlomodifydate(rs.getString("blolastmodifydate"));
				bbsBlogDatail.setBlomodifytime(rs.getString("blolastmodifytime"));
				bbsBlogDatail.setUname(rs.getString("uname"));
				BBSUser bbsUser = getUser(rs.getString("uname"));
				bbsBlogDatail.setBbsUser(bbsUser);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return bbsBlogDatail;
	}
	
	/**
	 * 取出所有的回复帖子
	 */
	public List<BBSReplyBlog> getReplyBlog(String bloid, int startPos, int pageSize) {
		List<BBSReplyBlog> replyBlogList = new ArrayList<BBSReplyBlog>();
		String sql = "select uname, rblcontent, "
				+ "rblreplydate, rblreplytime, rblmodifydate, rblmodifytime"
				+ " from bbsreplyblog where bloid = " +  "'" + bloid + "'"
				+ " limit " + startPos + ", " + pageSize;
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			while(rs.next()) {
				BBSReplyBlog bbsReplyBlog = new BBSReplyBlog();
				bbsReplyBlog.setRblContent(rs.getString("rblcontent"));
				bbsReplyBlog.setRblreplydate(rs.getString("rblreplydate"));
				bbsReplyBlog.setRblreplytime(rs.getString("rblreplytime"));
				bbsReplyBlog.setRblmodifydate(rs.getString("rblmodifydate"));
				bbsReplyBlog.setRblmodifytime(rs.getString("rblmodifytime"));
				BBSUser bbsUser = getUser(rs.getString("uname"));
				bbsReplyBlog.setBbsUser(bbsUser);
				bbsReplyBlog.setUname(rs.getString("uname"));
				replyBlogList.add(bbsReplyBlog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){ 
				dbDao.close(rs);
			}
		}
		return replyBlogList;
	}

	@Override
	public void post(BBSBlog bbsBlog) {
		String sql = "insert into bbsblog(blotitle,blocontent,bloinputdate, bloinputtime,"
				+ " blolastmodifydate, blolastmodifytime,uname,cboid,bloisdelete) "
				+ " value(?,?,CURRENT_DATE(),CURRENT_TIME(),CURRENT_DATE(),CURRENT_TIME(),?,?,'0')";
		dbDao.excuteDML(sql, bbsBlog.getBlotitle(), bbsBlog.getBlocontent(),
				bbsBlog.getUname(), bbsBlog.getCboid());
	}

	@Override
	public void reply(BBSReplyBlog bbsReplyBlog) {
		String sql = "INSERT INTO bbsreplyblog (rblcontent,rblreplydate,rblreplytime,rblmodifydate,rblmodifytime,uname,bloid,rblisdelete) "
				+ "VALUES(?,CURRENT_DATE(), CURRENT_TIME(),CURRENT_DATE(), CURRENT_TIME(),?,?,'0')";
		dbDao.excuteDML(sql, bbsReplyBlog.getRblContent(),bbsReplyBlog.getUname(),bbsReplyBlog.getBloid());
		
	}

	@Override
	public int getTotalBlog(String cboid) {
		int totalBlog = 0;
		String sql = "select count(*) cnt from bbsblog where cboid = '" + cboid + "'";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				totalBlog = rs.getInt("cnt");
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return totalBlog;
	}

	@Override
	public int getTotalRepbly(String bloid) {
		int totalReply = 0;
		String sql = "select count(*) cnt from bbsreplyblog where bloid = '" + bloid + "'";
		ResultSet rs = dbDao.excuteSelect(sql);
		try {
			if(rs.next()) {
				totalReply = rs.getInt("cnt");
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			dbDao.close(rs);
		}
		return totalReply;
	}
	

}
